package Fuction;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class Insert {
    //插入新课程
    public void insertNewCourse(Connection connection)throws  SQLException{
        Scanner sc=new Scanner(System.in);
        System.out.println("请输入课程编号：");
        String courseNum=sc.next();
        System.out.println("请输入课程名称：");
        String courseName=sc.next();
        System.out.println("请输入课程可容纳人数：");
        int coursePNum=sc.nextInt();
        System.out.println("请输入课程可修的学分：");
        double courseScore=sc.nextDouble();
        System.out.println("请仿照 例：2024-8-17_8:00的形式 输入该课程的补考时间,:");
        String courseRtime=sc.next();

        String sql="insert into course value(?,?,0,?,?,?)";
        PreparedStatement statement=connection.prepareStatement(sql);
        statement.setString(1,courseNum);
        statement.setString(2,courseName);
        statement.setInt(3,coursePNum);
        statement.setDouble(4,courseScore);
        statement.setString(5,courseRtime);
        statement.execute();
        System.out.println("新添课程成功");

    }
    //选课
    public void insertCourse(Connection connection)throws SQLException {
        Scanner sc=new Scanner(System.in);
        System.out.println("请输入选课学生姓名：");
        String chooseName=sc.next();
        String sql="select * from course ";
        System.out.println("以下是目前选课情况");
        PreparedStatement statement=connection.prepareStatement(sql);
        ResultSet n=statement.executeQuery();
        System.out.println("编号\t\t 名称\t已选人数\t容纳人数\t可修学分");
        while(n.next()){
            String id=n.getString("id");
            String courseName=n.getString("course_name");
            int nowSum=n.getInt("now_num");
            int sum=n.getInt("sum");
            double score=n.getDouble("course_score");
            System.out.print(id+"\t\t"+courseName+" \t "+nowSum+"\t\t "+sum+"\t\t "+score);
            System.out.println("");
        }

        String sql2="select * from student where name=? ";
        PreparedStatement statement2=connection.prepareStatement(sql2);
        statement2.setString(1,chooseName);
        ResultSet n2=statement2.executeQuery();
        int num=0;
        while(n2.next()){
            int courseNum=n2.getInt("course_num");
            num=courseNum;
            System.out.println("你目前已选科目数为："+courseNum);
            }
        System.out.print("分别是");

                String sql3="select * from score where student_name=?  ";
                PreparedStatement statement3=connection.prepareStatement(sql3);
                statement3.setString(1,chooseName);
                ResultSet n3=statement3.executeQuery();
                while(n3.next()){
                    String courseName=n3.getString("course_name");
                    System.out.println(courseName);
            }

        if (num==3){
            System.out.println("你的课程已选够3门，不用再选课");
        }else{
            System.out.println("你的课程还未选够，你还需选择"+(3-num)+"门课程");
            System.out.println("请输入你想要选择的课程的名称：");
            String courseName=sc.next();
            String sql4="insert into score value(?,?,NULL,NULL,NULL,NULL)";
            PreparedStatement statement4=connection.prepareStatement(sql4);
            statement4.setString(1,chooseName);
            statement4.setString(2,courseName);
            statement4.execute();
            String sql5="update student set course_num=? where name=? ";
            PreparedStatement statement5=connection.prepareStatement(sql5);
            statement5.setInt(1,num+1);
            statement5.setString(2,chooseName);
            statement5.execute();
            String sql6="update course set now_num=now_num+1 where course_name=? ";
            PreparedStatement statement6=connection.prepareStatement(sql6);
            statement6.setString(1,courseName);
            statement6.execute();
            System.out.println("选课成功");
        }

        System.out.println();
        statement.close();
    }

    //录入成绩
    public void insertScore(Connection connection) throws SQLException {
        Scanner sc=new Scanner(System.in);
        System.out.println("请输入学生姓名：");
        String studentName=sc.next();
        System.out.println("请输入你要填入成绩的科目");
        String courseName=sc.next();
        System.out.println("请输入该科目的成绩：");
        int courseScore= sc.nextInt();
        String sql="update score set course_score=? where student_name=? and course_name=?";
        PreparedStatement statement=connection.prepareStatement(sql);
        statement.setInt(1,courseScore);
        statement.setString(2,studentName);
        statement.setString(3,courseName);
        statement.execute();
        if (courseScore>=60 && courseName.equals("Java")){
            String sql2="update score set credit=? ,course_condition='通过' where student_name=? and course_name=?";
            PreparedStatement statement2=connection.prepareStatement(sql2);
            double courseCredit=courseScore/100.0*6*1.0;
            statement2.setDouble(1,courseCredit);
            statement2.setString(2,studentName);
            statement2.setString(3,courseName);
            statement2.execute();
            //更新到stundent
            String sql8="update student set sum_score=sum_score+? where name=?";
            PreparedStatement statement8= connection.prepareStatement(sql8);
            statement8.setDouble(1,courseCredit);
            statement8.setString(2,studentName);
            statement8.execute();
            System.out.println("成绩录入完毕");
        }else if (courseScore>=60){
            String sql3="update score set credit=? ,course_condition='通过' where student_name=? and course_name=?";
            PreparedStatement statement3=connection.prepareStatement(sql3);
            double courseCredit=courseScore/100.0*4*1.0;
            statement3.setDouble(1,courseCredit);
            statement3.setString(2,studentName);
            statement3.setString(3,courseName);
            statement3.execute();

            String sql8="update student set sum_score=sum_score+? where name=?";
            PreparedStatement statement8= connection.prepareStatement(sql8);
            statement8.setDouble(1,courseCredit);
            statement8.setString(2,studentName);
            statement8.execute();

            System.out.println("成绩录入完毕");
        }else {
            String sql4="update score set credit=0 ,course_condition='挂科' where student_name=? and course_name=?";
            PreparedStatement statement4=connection.prepareStatement(sql4);
            statement4.setString(1,studentName);
            statement4.setString(2,courseName);
            statement4.execute();

            //自动录入补考信息
            String sql5="update student set test_condition='挂科' where name=? ";
            PreparedStatement statement5=connection.prepareStatement(sql5);
            statement5.setString(1,studentName);
            statement5.execute();

            //录入补考时间
            String sql6="select * from course where course_name=? ";
            PreparedStatement statement6=connection.prepareStatement(sql6);
            statement6.setString(1,courseName);
            ResultSet n=statement6.executeQuery();
            while (n.next()){
                String time=n.getString("re_time");
                String sql7="update score set re_time=? where student_name=? and course_name=?";
                PreparedStatement statement7=connection.prepareStatement(sql7);
                statement7.setString(1,time);
                statement7.setString(2,studentName);
                statement7.setString(3,courseName);
                statement7.execute();
            }
            System.out.println("成绩录入完毕");
        }
    }
}
